﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;

namespace bdnt_pets_system_Bo.DataManager
{
    public class StationManager
    {
        //AccessHelperSQL accessHelper = AccessHelperSQL.GetInstance();
        AccessHelper accessHelper = AccessHelper.GetInstance();

        List<Tbl_Station> ConvertTableToObjs(DataTable dt)
        {
            List<Tbl_Station> returnlist = new List<Tbl_Station>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_Station
                {
                    Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                    StationNo = dr["StationNo"] == DBNull.Value ? "" : dr["StationNo"].ToString(),
                    StationName = dr["StationName"] == DBNull.Value ? "" : dr["StationName"].ToString(),
                    en_StationName = dr["en_StationName"] == DBNull.Value ? "" : dr["en_StationName"].ToString(),
                    ShopId = dr["ShopId"] == DBNull.Value ? 0 : Int32.Parse(dr["ShopId"].ToString()),
                    ShopNo = dr["ShopNo"] == DBNull.Value ? "" : dr["ShopNo"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString()
                });
            }
            return returnlist;
        }

        public List<Tbl_Station> GetObjListByCondition(Tbl_Station obj, int pageSize, int currentPage)
        {
            StringBuilder sql = new StringBuilder(@"select row_number() over(order by sta.Id desc) recordId,sta.* from Tbl_Station sta
                left join tbl_shop shop on shop.Id=sta.ShopId where 1=1");

            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.StationNo))
                    sql = sql.AppendFormat(@" and StationNo like '%{0}%'", obj.StationNo);
                if (!string.IsNullOrEmpty(obj.StationName))
                    sql = sql.AppendFormat(@" and StationName like '%{0}%'", obj.StationName);
            }

            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, "select re.* from (");
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            return ConvertTableToObjs(dt);
        }

        public List<Tbl_Station> GetAllObjList()
        {
            var sql = string.Format(@"select shop.Id ShopId,shop.ShopName,shop.en_ShopName,sta.* from tbl_shop shop
                left join Tbl_Station sta on shop.Id=sta.ShopId");
            var dt = accessHelper.ExcuteDataTable(sql);
            List<Tbl_Station> returnlist = new List<Tbl_Station>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_Station
                {
                    Id = dr["Id"] == DBNull.Value ? 0 : Convert.ToInt32(dr["Id"]),
                    en_StationName = dr["en_StationName"] == DBNull.Value ? "" : dr["en_StationName"].ToString(),
                    StationName = dr["StationName"] == DBNull.Value ? "" : dr["StationName"].ToString(),
                    ShopId = dr["ShopId"] == DBNull.Value ? 0 : Int32.Parse(dr["ShopId"].ToString()),
                    StationNo = dr["StationNo"] == DBNull.Value ? "" : dr["StationNo"].ToString(),
                    ShopName = dr["ShopName"] == DBNull.Value ? "" : dr["ShopName"].ToString(),
                    en_ShopName = dr["en_ShopName"] == DBNull.Value ? "" : dr["en_ShopName"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString()
                });
            }
            return returnlist;
        }

        public int GetObjListByConditionCount(Tbl_Station obj)
        {
            StringBuilder sql = new StringBuilder(@"select count(shop.Id) allCount 
           from tbl_shop shop
                left join Tbl_Station sta on shop.Id=sta.ShopId where 1=1");
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.StationNo))
                    sql = sql.AppendFormat(@" and StationNo like '%{0}%'", obj.StationNo);
                if (!string.IsNullOrEmpty(obj.StationName))
                    sql = sql.AppendFormat(@" and StationName like '%{0}%'", obj.StationName);
            }
            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }


        public List<Tbl_StationMemebers> GetStationMemebersByCodition(string stationIds)
        {
            if (string.IsNullOrEmpty(stationIds))
                return null;
            var sql = string.Format(@"
            select st.ShopId,sp.ShopName,sp.en_ShopName,st.Id StationId,st.StationName,
            st.en_StationName,sm.EmpId,
            em.EmpName,sm.IsManager from Tbl_Station st 
            left join tbl_Shop sp on sp.Id=st.ShopId
            left join Tbl_StationMemebers sm on st.Id=sm.Stationid
            left join Tbl_Employee em on sm.EmpId=em.id
            where st.Id in ({0})", stationIds);
            var dt = accessHelper.ExcuteDataTable(sql);
            List<Tbl_StationMemebers> returnlist = new List<Tbl_StationMemebers>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_StationMemebers
                {
                    StationId = dr["StationId"] == DBNull.Value ? 0 : Convert.ToInt32(dr["StationId"]),
                    EmpId = dr["EmpId"] == DBNull.Value ? 0 : Convert.ToInt32(dr["EmpId"]),
                    en_StationName = dr["en_StationName"] == DBNull.Value ? "" : dr["en_StationName"].ToString(),
                    StationName = dr["StationName"] == DBNull.Value ? "" : dr["StationName"].ToString(),
                    EmpName = dr["EmpName"] == DBNull.Value ? "" : dr["EmpName"].ToString(),
                    IsManager = dr["IsManager"] == DBNull.Value ? false : dr["IsManager"].ToString() == "1" ? true : false,
                    en_ShopName = dr["en_ShopName"] == DBNull.Value ? "" : dr["en_ShopName"].ToString(),
                    ShopName = dr["ShopName"] == DBNull.Value ? "" : dr["ShopName"].ToString(),
                    ShopId = dr["ShopId"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ShopId"])
                });
            }
            return returnlist;
        }

        public bool SaveObj(Tbl_Station cuObj)
        {
            string sql = "";
            if (cuObj.Id == 0)
                sql = string.Format(@"INSERT INTO Tbl_Station
           (StationNo,StationName,en_StationName,ShopName,en_shopName,ShopId,Remark)
            VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", cuObj.StationNo, cuObj.StationName, cuObj.en_StationName,
                                                        cuObj.ShopName, cuObj.en_ShopName, cuObj.ShopId,cuObj.Remark);

            else sql = string.Format(@"UPDATE Tbl_Station
               SET  StationNo='{0}'
                 ,StationName ='{1}'
                 ,en_StationName ='{2}'
                 ,ShopName ='{3}'
                 ,en_shopName ='{4}'
                 ,ShopId ='{5}'
                ,Remark='{6}'
             WHERE Id={7}", cuObj.StationNo, cuObj.StationName,
                          cuObj.en_StationName, cuObj.ShopName, cuObj.en_ShopName, cuObj.ShopId, cuObj.Remark,cuObj.Id);
            return accessHelper.ExcultData(sql);
        }

        public bool DeleteCuObj(Int32 objId)
        {
            string sql = string.Format(@"delete from Tbl_Station where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }

    }
}
